﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using web.data.entities;
using System.Configuration;
using Webdiyer.WebControls.Mvc;
using web.component;

namespace web.bussines
{
    public class profile_service
    {
        public PagedList<employee> getPageListEmployee(int pageindex, int pagesite, string text_search, string option_search, int? department_id, byte? employee_status, DateTime? birth_day, int? b_day, int? b_month, int? b_year, bool? join_dang, string study_level, string blood_group, string current_address, int? start_date_insurance, string positions, string foreign_language, string house_land_status, int? date_join, string type_list_employee, ref int total_record, bool? gender, int? army_join, string ranks, int? school_id, string marital_status)
        {
            using (var data = new baseEntities())
            {
                IQueryable<employee> list=null;
                if (string.IsNullOrEmpty(type_list_employee))
                {
                    list = (from p in data.employees where p.employee_status==1
                            select p);
                }
                else
                {
                    if (type_list_employee == conts_global.Employee_Family)
                    {
                        list = (from f in data.employees
                                join p in data.employee_family on  f.tbl_employee_family.FirstOrDefault().employee_id equals p.employee_id
                                select f);
                    }
                    else if (type_list_employee == conts_global.Employee_Foreign_Countries)
                    {
                        list = (from f in data.employees
                                join p in data.employee_foreign_countries on f.tbl_employee_foreign_countries.FirstOrDefault().employee_id equals p.employee_id
                                select f);

                    }
                    else if (type_list_employee == conts_global.Employee_School)
                    {
                        list = (from f in data.employees
                                join p in data.employee_school on f.tbl_employee_school.FirstOrDefault().employee_id equals p.employee_id
                                select f);

                    }
                    else if (type_list_employee == conts_global.Employee_Insurance)
                    {
                        list = (from f in data.employees
                                join p in data.employee_insurance on f.tbl_employee_insurance.FirstOrDefault().employee_id equals p.employee_id
                                select f);
                    }
                    else if (type_list_employee == conts_global.Employee_Job_Severance)
                    {
                        list = (from f in data.employees
                                join p in data.employee_job_severance on f.tbl_employee_job_severance.FirstOrDefault().employee_id equals p.employee_id
                                select f);
                    }
                    else if (type_list_employee == conts_global.Employee_Medical_Records)
                    {
                        list = (from f in data.employees
                                join p in data.employee_medical_records on f.tbl_employee_medical_records.FirstOrDefault().employee_id equals p.employee_id
                                select f);
                    }
                    else if (type_list_employee == conts_global.Employee_Rotation)
                    {
                        list = (from f in data.employees
                                join p in data.employee_rotation on f.tbl_employee_rotation.FirstOrDefault().employee_id equals p.employee_id
                                select f);
                    }
                    else if (type_list_employee == conts_global.Employee_School)
                    {
                        list = (from f in data.employees
                                join p in data.employee_school on f.tbl_employee_school.FirstOrDefault().school_id equals p.school_id
                                select f);
                    }
                    else if (type_list_employee == conts_global.Employee_Visa_Passport)
                    {
                        list = (from f in data.employees
                                join p in data.employee_visa_passport on f.tbl_employee_visa_passport.FirstOrDefault().employee_id equals p.employee_id
                                select f);
                    }
                    else if (type_list_employee == conts_global.Employee_Contract)
                    {
                        var star_date = DateTime.Now.Date.AddDays(-10);
                        list = (from f in data.employees
                                join p in data.contracts on f.tbl_contract.FirstOrDefault().employee_id equals p.employee_id 
                                where p.enable_end>=star_date && p.enable_end <=DateTime.Now
                                select f);
                    }
                    else {
                        list = (from p in data.employees where p.employee_status==1
                                select p);
                    }
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    if (!string.IsNullOrEmpty(option_search))
                    {
                        if (option_search == "employee_code")
                        {
                            list = list.Where(p => p.employee_code.Contains(text_search));
                        }
                        else if (option_search=="full_name")
                        {
                            list = list.Where(p => p.full_name.Contains(text_search));
                        }
                        else
                            list = list.Where(p => p.employee_code.Contains(text_search) || p.full_name.Contains(text_search) || p.email.Contains(text_search));
                    }
                    else
                    {
                        list = list.Where(p => p.employee_code.Contains(text_search) || p.full_name.Contains(text_search) || p.email.Contains(text_search));
                    } 
                }

                if (employee_status != null)
                {
                    if (employee_status.Value.ToString() != string.Empty) {
                        list = list.Where(p => p.employee_status == employee_status);
                    }
                    
                }

                if (department_id != null)
                {
                    list = (from p in list
                            //join d in data.department_employee on p.employee_id equals d.employee_id
                            join y in data.department_employee on p.tbl_department_employee.FirstOrDefault().employee_id equals y.employee_id
                            where y.departments_id == department_id
                            select p);
                }
                if (school_id != null)
                {
                    list = (from p in list
                            join y in data.employee_school on p.employee_id equals y.employee_id
                            where y.school_id == school_id
                            select p);
                }

                if (birth_day != null)
                {
                    list = list.Where(p => p.birth_day == birth_day);
                }

                if (b_day != null && b_day != 0)
                {
                    list = list.Where(p => p.b_day == b_day);
                }

                if (b_month != null && b_month != 0)
                {
                    list = list.Where(p => p.b_month == b_month);
                }

                if (b_year != null && b_year != 0)
                {
                    list = list.Where(p => p.b_year == b_year);
                }

                if (join_dang != null)
                {
                    if (join_dang == true)
                    {
                        list = list.Where(p => p.dateIn_dang != null);
                    }
                    else
                    {
                        list = list.Where(p => p.dateIn_dang == null);
                    }
                }

                if (!string.IsNullOrEmpty(study_level))
                {
                    list = (from p in list
                            join s in data.employee_school on p.employee_id equals s.employee_id
                            where s.study_level.Contains(study_level)
                            select p);
                }

                if (!string.IsNullOrEmpty(blood_group))
                {
                    list = (from p in list
                            join r in data.employee_medical_records on p.employee_id equals r.employee_id
                            where r.blood_group == blood_group
                            select p);
                }

                if (!string.IsNullOrEmpty(current_address))
                {
                    list = list.Where(p => p.current_address.Contains(current_address) || p.hometown.Contains(current_address) || p.household_registration.Contains(current_address));
                }

                if (start_date_insurance != null)
                {
                    
                    if (start_date_insurance > 0)
                    {
                        var insurance_date = DateTime.Now.AddYears(0 - (int)start_date_insurance);
                        list = (from p in list
                                //join i in data.employee_insurance on p.employee_id equals i.employee_id
                                join y in data.employee_insurance on p.tbl_employee_insurance.FirstOrDefault().employee_id equals y.employee_id
                                where y.start_date <= insurance_date
                                select p);
                    }
                }

                if (!string.IsNullOrEmpty(positions))
                {
                    list = list.Where(p => p.positions.Contains(positions));
                }

                if (!string.IsNullOrEmpty(foreign_language))
                {
                    list = list.Where(p => p.foreign_language.Contains(foreign_language));
                }

                if (!string.IsNullOrEmpty(house_land_status))
                {
                    list = list.Where(p => p.house_land_status.Contains(house_land_status));
                }

                if (date_join != null)
                {
                    if (date_join > 0)
                    {
                        var j_date=DateTime.Now.AddYears(0-(int)date_join);
                        list = list.Where(p => p.date_join.Value <= j_date);
                    }
                    
                }

                if (gender != null)
                {
                    if (gender.ToString() != string.Empty)
                    {
                        list = list.Where(p => p.gender == gender);
                    }
                    
                }
                if (army_join != null)
                {
                    list = list.Where(p => p.army_join.Value.Year == army_join); 
                }

                if (!string.IsNullOrEmpty(marital_status))
                {
                    list = list.Where(p => p.marital_status.Contains(marital_status));
                }

                if (!string.IsNullOrEmpty(ranks))
                {
                    list = list.Where(p => p.ranks.Contains(ranks));
                }

                list = list.OrderByDescending(p => p.employee_id);

                var result = (from e in list
                              select e);

                total_record = result.ToList().Count;

                return result.ToPagedList(pageindex, pagesite); 
            }
        }

        public employee getEmployeeByID(int id)
        {
            using (var data = new baseEntities())
            {
                var emp = data.employees.Where(p => p.employee_id == id).FirstOrDefault();
                return emp;
            }
        }

        public List<employee_family> getListEmployeeFamilyByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_family
                                     where p.employee_id == id //&& p.family_status == 1
                                     select p).ToList();
                return list;
            }
        }

        //public List<employee_house_land> getListEmployeeHouseLandByEmployee(int id)
        //{
        //    using (var data = new baseEntities())
        //    {
        //        var list = (from p in data.employee_house_land
        //                    where p.employee_id == id && p.house_land_status == 1
        //                    select p).ToList();
        //        return list;
        //    }
        //}

        public List<employee_insurance> getListEmployeeInsuranceByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_insurance
                            where p.employee_id == id //&& p.insurance_status == 1
                            select p).ToList();
                return list;
            }
        }

        public List<employee_job_severance> getListEmployeeJobSeveranceByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_job_severance
                            where p.employee_id == id
                            select p).ToList();
                return list;
            }
        }

        public List<employee_medical_records> getListEmployeeMedicalRecodByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_medical_records
                            where p.employee_id == id 
                            select p).ToList();
                return list;
            }
        }

        public List<employee_rotation> getListEmployeeRotationByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_rotation
                            where p.employee_id == id //&& p.rotation_status == 1
                            select p).ToList();
                return list;
            }
        }

        public List<employee_school> getListEmployeeSchoolByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_school
                            where p.employee_id == id 
                            select p).ToList();
                return list;
            }
        }

        public List<employee_visa_passport> getListEmployeeVisaPassportByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_visa_passport
                            where p.employee_id == id //&& p.visa_passport_status == 1
                            select p).ToList();
                return list;
            }
        }

        public school getSchoolByID(int id)
        {
            using (var data = new baseEntities())
            {
                var school = (from p in data.schools
                              where p.school_id == id
                              select p).FirstOrDefault();
                return school;
            }
        }

        public List<contract> getListContractByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.contracts
                            where p.employee_id == id //&& p.contract_status == 1
                            select p).ToList();
                list = list.OrderByDescending(p => p.contract_id).ToList();
                return list;
            }
        }

        public contract getLastContractByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.contracts
                            where p.employee_id == id //&& p.contract_status == 1
                            select p).ToList();
                list = list.OrderByDescending(p => p.contract_id).ToList();
                return list.FirstOrDefault();
            }
        }

        public type_contract getTypeContractByID(int id)
        {
            using (var data = new baseEntities())
            {
                var contracts = (from p in data.type_contract
                              where p.type_contract_id == id
                              select p).FirstOrDefault();
                return contracts;
            }
        }

        public departments getDepartmentByID(int id)
        {
            using (var data = new baseEntities())
            {
                var depart = (from p in data.departments
                                 where p.departments_id == id
                                 select p).FirstOrDefault();
                return depart;
            }
        }

        public List<departments> getListDepartment()
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.departments
                            where p.departments_status == 1
                            select p).ToList();
                return list;
            }
        }

        public List<employee_family> getListEmployeeFamily(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_family
                            where p.employee_id == id && p.family_status == 1
                            select p).ToList();
                return list;
            }
        }

        public List<employee_foreign_countries> getListEmployeeForeignCountryByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_foreign_countries
                            where p.employee_id == id 
                            select p).ToList();
                return list;
            }
        }

        public PagedList<employee_reward> getListEmployee_QNCN_CNVQP(int pageindex, int pagesite, string option_search, int? department_id, byte? employee_status, DateTime? from_date, DateTime? to_date, int? type_report)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_reward
                        select p);
                if (employee_status != null)
                {
                    list = list.Where(p => p.employee_status == employee_status);
                }

                if (from_date != null && to_date != null)
                {
                    list = (from p in list
                            where p.date_join >= from_date && p.date_join <= to_date
                            select p);
                }

                if (!string.IsNullOrEmpty(option_search))
                {
                    list = list.Where(p => p.full_name.Contains(option_search) || p.employee_code.Contains(option_search) || p.serial_number_military.Contains(option_search));
                }

                if (type_report == 1)
                { 
                    //QNCN
                    list = list.Where(p => (new int[] { 14, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26 }).Contains(p.object_employee_id??0));
                }

                if (type_report == 2)
                { 
                    //CNVQP
                    list = list.Where(p => (new int[] { 15, 27 }).Contains(p.object_employee_id ?? 0));
                }

                if (type_report ==3)
                {
                    list = list.Where(p => p.enable_end != null && p.enable_end.Value >= from_date && p.enable_end <= to_date);
                }

                list = list.OrderByDescending(p => p.employee_id);

                return list.ToPagedList(pageindex, pagesite);
            }
        }

        public List<employee_reward> getListEmployee_QNCN_CNVQP_export(string option_search, int? department_id, byte? employee_status, DateTime? from_date, DateTime? to_date, int? type_report)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_reward
                            select p);
                if (employee_status != null)
                {
                    list = list.Where(p => p.employee_status == employee_status);
                }

                if (from_date != null && to_date != null)
                {
                    list = (from p in list
                            where p.date_join >= from_date && p.date_join <= to_date
                            select p);
                }

                if (!string.IsNullOrEmpty(option_search))
                {
                    list = list.Where(p => p.full_name.Contains(option_search) || p.employee_code.Contains(option_search) || p.serial_number_military.Contains(option_search));
                }

                if (type_report == 1)
                {
                    //QNCN
                    list = list.Where(p => (new int[] { 14, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26 }).Contains(p.object_employee_id ?? 0));
                }

                if (type_report == 2)
                {
                    //CNVQP
                    list = list.Where(p => (new int[] { 15, 27 }).Contains(p.object_employee_id ?? 0));
                }

                if (type_report == 3)
                {
                    list = list.Where(p => p.enable_end != null && p.enable_end.Value >= from_date && p.enable_end <= to_date);
                }

                list = list.OrderByDescending(p => p.employee_id);

                return list.ToList();
            }
        }
        
        public List<departments> getListDepartmentsByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list_departments = (from e in data.employees
                                        join de in data.department_employee on e.employee_id equals de.employee_id into r0
                                        from e2 in r0.DefaultIfEmpty()
                                        join d in data.departments on e2.department_employee_id equals d.departments_id
                                        where e.employee_id == id && d.departments_status == 1
                                        select d);
                return list_departments.ToList();
            }
        }

        public departments getTopOneDepartmentsByEmployee(int id)
        {
            using (var data = new baseEntities())
            {
                var list_departments = (from e in data.departments
                                        join de in data.department_employee on e.departments_id equals de.departments_id 
                                        where de.employee_id == id && e.departments_status == 1
                                        select e);

                list_departments = list_departments.OrderByDescending(p => p.departments_id);
                return list_departments.FirstOrDefault();
            }
        }

        public employee_ranks_object getEmployee_ranks_objectByDepartmentIDandObject(int department_id, int object_id)
        {
            using (var data = new baseEntities())
            {
                var ranks_object = (from p in data.employee_ranks_object
                            where p.departments_id==department_id && p.object_employee_id==object_id
                            select p).FirstOrDefault();

                return ranks_object;
            }
        }

        public PagedList<departments> getListEmployee_DG_CB_QH(int pageindex, int pagesize, string text_search, DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.departments
                            select p);


               
                if (from_date != null && to_date != null)
                {
                    //list = (from p in list
                    //        where p.date_join >= from_date && p.date_join <= to_date
                    //        select p);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    //list = list.Where(p => p.full_name.Contains(option_search) || p.employee_code.Contains(option_search) || p.serial_number_military.Contains(option_search));
                }

                list = list.OrderBy(p => p.departments_id);

                return list.ToPagedList(pageindex, pagesize);
            }
        }

        public List<departments> getListEmployee_DG_CB_QH_export(string text_search, DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.departments
                            select p);



                if (from_date != null && to_date != null)
                {
                    //list = (from p in list
                    //        where p.date_join >= from_date && p.date_join <= to_date
                    //        select p);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    //list = list.Where(p => p.full_name.Contains(option_search) || p.employee_code.Contains(option_search) || p.serial_number_military.Contains(option_search));
                }

                list = list.OrderBy(p => p.departments_id);

                return list.ToList();
            }
        }

        public int getNoHDDHBydepartmentID(int id)
        {
            using (var data = new baseEntities())
            {
                var re = (from p in data.employees
                          join c in data.contracts on p.employee_id equals c.employee_id into r0
                          from e0 in r0.DefaultIfEmpty()
                          join d in data.department_employee on e0.employee_id equals d.employee_id into r1
                          from e1 in r1.DefaultIfEmpty()
                          where e1.departments_id == id && (e0.type_contract_id == 2) && e1.departments_id != null
                          group e1 by new { e1.departments_id, e0.type_contract_id } into r2
                          select r2.Count()).FirstOrDefault();
                return re;
            }
        }

        public int getNoHDNHBydepartmentID(int id)
        {
            using (var data = new baseEntities())
            {
                var re = (from p in data.employees
                          join c in data.contracts on p.employee_id equals c.employee_id into r0
                          from e0 in r0.DefaultIfEmpty()
                          join d in data.department_employee on e0.employee_id equals d.employee_id into r1
                          from e1 in r1.DefaultIfEmpty()
                          where e1.departments_id == id && (e0.type_contract_id == 3) && e1.departments_id != null
                          group e1 by new { e1.departments_id, e0.type_contract_id } into r2
                          select r2.Count()).FirstOrDefault();
                return re;
            }
        }

        public PagedList<employee_reward_individual> getListEmployee_individual(int pageindex, int pagesize, string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_reward_individual
                            select p);
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list = list.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => p.full_name.Contains(text_search) || p.employee_code.Contains(text_search));
                }
                List<int> arr_id = new List<int>();
                //get by list id employee
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    {
                                        arr_id.Add(int.Parse(arrs[i]));
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0)
                {
                    list = list.Where(p => arr_id.Contains(p.employee_id));
                }

                list = list.OrderBy(p => p.signature_date);

                //total_all
                total_all = new List<int>();

                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no
                              select t).Count();
                    total_all.Add(co);
                }

                //total_skill_reward_1
                total_skill_reward_1 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 1
                              select t).Count();
                    total_skill_reward_1.Add(co);
                }

                //total_skill_reward_2
                total_skill_reward_2 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 2
                              select t).Count();
                    total_skill_reward_2.Add(co);
                }

                //total_skill_reward_3
                total_skill_reward_3 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 3
                              select t).Count();
                    total_skill_reward_3.Add(co);
                }

                return list.ToPagedList(pageindex, pagesize);
            }
        }

        public PagedList<proc_employee_reward_individual_total_Result> getListEmployee_individual_total(int pageindex, int pagesize, string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {

                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                var total_individual = data.employee_reward_individual_total(text_search, from_date, to_date);

                var list = (from p in total_individual
                                                                            select p).ToList();

                List<int> arr_id=new List<int>();

                //get by list id employee
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    { 
                                        arr_id.Add(int.Parse(arrs[i]));   
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0) {
                    list = list.Where(p => arr_id.Contains(p.employee_id)).ToList();
                }

                list = list.OrderBy(p => p.employee_id).ToList();

                IQueryable<proc_employee_reward_individual_total_Result> result = (from p in list
                              select new proc_employee_reward_individual_total_Result
                              {
                                  employee_id = p.employee_id,
                                  total_reward = p.total_reward,
                                  total_reward_1 = p.total_reward_1,
                                  total_reward_2 = p.total_reward_2,
                                  total_reward_3 = p.total_reward_3,
                                  total_reward_4 = p.total_reward_4,
                                  total_reward_5 = p.total_reward_5,
                                  total_reward_6 = p.total_reward_6,
                                  total_reward_7 = p.total_reward_7,
                                  total_reward_8 = p.total_reward_8,
                                  total_reward_9 = p.total_reward_9,
                                  total_reward_10 = p.total_reward_10,
                                  total_reward_11 = p.total_reward_11,
                                  total_reward_12 = p.total_reward_12,
                                  total_reward_13 = p.total_reward_13,
                                  total_reward_14 = p.total_reward_14,
                                  total_reward_15 = p.total_reward_15,
                                  total_reward_16 = p.total_reward_16,
                                  full_name = p.full_name
                              }).AsQueryable();

                //total_all
                total_all = new List<int>();


                var query = (from e in result
                             group e by 1 into eg
                             select new
                             {
                                 TypeOfInvestment = eg.Key,
                                 re_1 = eg.Sum(y => y.total_reward_1),
                                 re_2 = eg.Sum(y => y.total_reward_2),
                                 re_3 = eg.Sum(y => y.total_reward_3),
                                 re_4 = eg.Sum(y => y.total_reward_4),
                                 re_5 = eg.Sum(y => y.total_reward_5),
                                 re_6 = eg.Sum(y => y.total_reward_6),
                                 re_7 = eg.Sum(y => y.total_reward_7),
                                 re_8 = eg.Sum(y => y.total_reward_8),
                                 re_9 = eg.Sum(y => y.total_reward_9),
                                 re_10 = eg.Sum(y => y.total_reward_10),
                                 re_11 = eg.Sum(y => y.total_reward_11),
                                 re_12 = eg.Sum(y => y.total_reward_12),
                                 re_13 = eg.Sum(y => y.total_reward_13),
                                 re_14=eg.Sum(y=>y.total_reward_14),
                                 re_15=eg.Sum(y=>y.total_reward_15),
                                 re_16=eg.Sum(y=>y.total_reward_16)

                             }).FirstOrDefault();
                //total_all
                total_all = new List<int>();
                if (query!=null) { 
                total_all.Add(query.re_1);
                total_all.Add(query.re_2);
                total_all.Add(query.re_3);
                total_all.Add(query.re_4);
                total_all.Add(query.re_5);
                total_all.Add(query.re_6);
                total_all.Add(query.re_7);
                total_all.Add(query.re_8);
                total_all.Add(query.re_9);
                total_all.Add(query.re_10);
                total_all.Add(query.re_11);
                total_all.Add(query.re_12);
                total_all.Add(query.re_13);
                total_all.Add(query.re_14);
                total_all.Add(query.re_15);
                total_all.Add(query.re_16);
                }
                return result.ToPagedList(pageindex, pagesize);
            }
        }

        public bool isNumber(string val)
        {
            int Num;
            bool isNum = int.TryParse(val, out Num);
            if (isNum)
                return true;
            else
                return false;
        }

        public List<proc_employee_reward_individual_total_Result> getListEmployee_individual_export_total(string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {

                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                var total_individual = data.employee_reward_individual_total(text_search, from_date, to_date);

                var list = (from p in total_individual
                            select p).ToList();

                 List<int> arr_id=new List<int>();

                //get by list id employee
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    { 
                                        arr_id.Add(int.Parse(arrs[i]));   
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0)
                {
                    list = list.Where(p => arr_id.Contains(p.employee_id)).ToList();
                }
                
                list = list.OrderBy(p => p.employee_id).ToList();

                IQueryable<proc_employee_reward_individual_total_Result> result = (from p in list
                                                                                   select new proc_employee_reward_individual_total_Result
                                                                                   {
                                                                                       employee_id = p.employee_id,
                                                                                       total_reward = p.total_reward,
                                                                                       total_reward_1 = p.total_reward_1,
                                                                                       total_reward_2 = p.total_reward_2,
                                                                                       total_reward_3 = p.total_reward_3,
                                                                                       total_reward_4 = p.total_reward_4,
                                                                                       total_reward_5 = p.total_reward_5,
                                                                                       total_reward_6 = p.total_reward_6,
                                                                                       total_reward_7 = p.total_reward_7,
                                                                                       total_reward_8 = p.total_reward_8,
                                                                                       total_reward_9 = p.total_reward_9,
                                                                                       total_reward_10 = p.total_reward_10,
                                                                                       total_reward_11 = p.total_reward_11,
                                                                                       total_reward_12 = p.total_reward_12,
                                                                                       total_reward_13 = p.total_reward_13,
                                                                                       total_reward_14=p.total_reward_14,
                                                                                       total_reward_15=p.total_reward_15,
                                                                                       total_reward_16=p.total_reward_16,
                                                                                       full_name = p.full_name
                                                                                   }).AsQueryable();

                //total_all
                total_all = new List<int>();


                var query = (from e in result
                             group e by 1 into eg
                             select new
                             {
                                 TypeOfInvestment = eg.Key,
                                 re_1 = eg.Sum(y => y.total_reward_1),
                                 re_2 = eg.Sum(y => y.total_reward_2),
                                 re_3 = eg.Sum(y => y.total_reward_3),
                                 re_4 = eg.Sum(y => y.total_reward_4),
                                 re_5 = eg.Sum(y => y.total_reward_5),
                                 re_6 = eg.Sum(y => y.total_reward_6),
                                 re_7 = eg.Sum(y => y.total_reward_7),
                                 re_8 = eg.Sum(y => y.total_reward_8),
                                 re_9 = eg.Sum(y => y.total_reward_9),
                                 re_10 = eg.Sum(y => y.total_reward_10),
                                 re_11 = eg.Sum(y => y.total_reward_11),
                                 re_12 = eg.Sum(y => y.total_reward_12),
                                 re_13 = eg.Sum(y => y.total_reward_13),
                                 re_14=eg.Sum(y=>y.total_reward_14),
                                 re_15=eg.Sum(y=>y.total_reward_15),
                                 re_16=eg.Sum(y=>y.total_reward_16)

                             }).FirstOrDefault();
                //total_all
                total_all = new List<int>();
                if (query != null)
                {
                    total_all.Add(query.re_1);
                    total_all.Add(query.re_2);
                    total_all.Add(query.re_3);
                    total_all.Add(query.re_4);
                    total_all.Add(query.re_5);
                    total_all.Add(query.re_6);
                    total_all.Add(query.re_7);
                    total_all.Add(query.re_8);
                    total_all.Add(query.re_9);
                    total_all.Add(query.re_10);
                    total_all.Add(query.re_11);
                    total_all.Add(query.re_12);
                    total_all.Add(query.re_13);
                    total_all.Add(query.re_14);
                    total_all.Add(query.re_15);
                    total_all.Add(query.re_16);
                }
                return result.ToList();
            }
        }

        public List<employee_reward_individual> getListEmployee_individual_export(string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_reward_individual
                            select p);
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list = list.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => p.full_name.Contains(text_search) || p.employee_code.Contains(text_search));
                }

                List<int> arr_id = new List<int>();
                //get by list id employee
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    {
                                        arr_id.Add(int.Parse(arrs[i]));
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0)
                {
                    list = list.Where(p => arr_id.Contains(p.employee_id));
                }

                list = list.OrderBy(p => p.signature_date);

                //total_all
                total_all = new List<int>();

                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no
                              select t).Count();
                    total_all.Add(co);
                }

                //total_skill_reward_1
                total_skill_reward_1 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 1
                              select t).Count();
                    total_skill_reward_1.Add(co);
                }

                //total_skill_reward_2
                total_skill_reward_2 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 2
                              select t).Count();
                    total_skill_reward_2.Add(co);
                }

                //total_skill_reward_3
                total_skill_reward_3 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 3
                              select t).Count();
                    total_skill_reward_3.Add(co);
                }

                return list.ToList();
            }
        }

        public PagedList<employee_reward_collective> getListEmployee_collective(int pageindex, int pagesize, string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_reward_collective
                            select p);
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date =DateTime.Now;
                }

                //from_date = Convert.ToDateTime(String.Format("{0:dd/MM/yyyy}", from_date));
                //to_date = Convert.ToDateTime(String.Format("{0:dd/MM/yyyy}", to_date));

                if (from_date != null && to_date != null)
                {
                    list = list.Where(p => p.signature_date.Value >= from_date && p.signature_date <= to_date);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => p.departments_name.Contains(text_search) || p.reward_name_group.Contains(text_search));
                }

                List<int> arr_id = new List<int>();
                //get by list id department
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    {
                                        arr_id.Add(int.Parse(arrs[i]));
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0)
                {
                    list = list.Where(p => arr_id.Contains(p.departments_id??0));
                }

                list = list.OrderBy(p => p.signature_date);

                //total_all
                total_all = new List<int>();

                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                                    where t.form_reward == no
                                    select t).Count();
                    total_all.Add(co);
                }

                //total_skill_reward_1
                total_skill_reward_1 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                                               where t.form_reward == no && t.skill_reward==1
                                               select t).Count();
                    total_skill_reward_1.Add(co);
                }

                //total_skill_reward_2
                total_skill_reward_2 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                                               where t.form_reward == no && t.skill_reward == 2
                                               select t).Count();
                    total_skill_reward_2.Add(co);
                }

                //total_skill_reward_3
                total_skill_reward_3 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                                        where t.form_reward == no && t.skill_reward == 3
                                       select t).Count();
                    total_skill_reward_3.Add(co);
                }

                    return list.ToPagedList(pageindex, pagesize);
            }
        }

        public List<employee_reward_collective> getListEmployee_collective_export(string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employee_reward_collective
                            select p);
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list = list.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => p.departments_name.Contains(text_search) || p.reward_name_group.Contains(text_search));
                }

                List<int> arr_id = new List<int>();
                //get by list id department
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    {
                                        arr_id.Add(int.Parse(arrs[i]));
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0)
                {
                    list = list.Where(p => arr_id.Contains(p.departments_id ?? 0));
                }

                list = list.OrderBy(p => p.signature_date);

                //total_all
                total_all = new List<int>();

                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no
                              select t).Count();
                    total_all.Add(co);
                }

                //total_skill_reward_1
                total_skill_reward_1 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 1
                              select t).Count();
                    total_skill_reward_1.Add(co);
                }

                //total_skill_reward_2
                total_skill_reward_2 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 2
                              select t).Count();
                    total_skill_reward_2.Add(co);
                }

                //total_skill_reward_3
                total_skill_reward_3 = new List<int>();
                for (int i = 0; i < 16; i++)
                {
                    var no = (i + 1).ToString();
                    var co = (from t in list
                              where t.form_reward == no && t.skill_reward == 3
                              select t).Count();
                    total_skill_reward_3.Add(co);
                }

                return list.ToList();
            }
        }

        public PagedList<proc_employee_reward_collective_total_Result> getListEmployee_collective_total(int pageindex, int pagesize, string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {

                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                var total_collective = data.employee_reward_collective_total(text_search, from_date, to_date);

                var list = (from p in total_collective
                            select p).ToList();

                List<int> arr_id=new List<int>();

                //get by list id department
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    { 
                                        arr_id.Add(int.Parse(arrs[i]));   
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0)
                {
                    list = list.Where(p => arr_id.Contains(p.departments_id ?? 0)).ToList();
                }
                

                list = list.OrderBy(p => p.departments_id).ToList();

                IQueryable<proc_employee_reward_collective_total_Result> result = (from p in list
                                                                                   select new proc_employee_reward_collective_total_Result
                                                                                   {
                                                                                       departments_id = p.departments_id,
                                                                                       total_reward = p.total_reward,
                                                                                       total_reward_1 = p.total_reward_1,
                                                                                       total_reward_2 = p.total_reward_2,
                                                                                       total_reward_3 = p.total_reward_3,
                                                                                       total_reward_4 = p.total_reward_4,
                                                                                       total_reward_5 = p.total_reward_5,
                                                                                       total_reward_6 = p.total_reward_6,
                                                                                       total_reward_7 = p.total_reward_7,
                                                                                       total_reward_8 = p.total_reward_8,
                                                                                       total_reward_9 = p.total_reward_9,
                                                                                       total_reward_10 = p.total_reward_10,
                                                                                       total_reward_11 = p.total_reward_11,
                                                                                       total_reward_12 = p.total_reward_12,
                                                                                       total_reward_13 = p.total_reward_13,
                                                                                       total_reward_14 = p.total_reward_14,
                                                                                       total_reward_15 = p.total_reward_15,
                                                                                       total_reward_16 = p.total_reward_16,
                                                                                       departments_name = p.departments_name
                                                                                   }).AsQueryable();

                //total_all
                total_all = new List<int>();


                var query = (from e in result
                             group e by 1 into eg
                             select new
                             {
                                 TypeOfInvestment = eg.Key,
                                 re_1 = eg.Sum(y => y.total_reward_1),
                                 re_2 = eg.Sum(y => y.total_reward_2),
                                 re_3 = eg.Sum(y => y.total_reward_3),
                                 re_4 = eg.Sum(y => y.total_reward_4),
                                 re_5 = eg.Sum(y => y.total_reward_5),
                                 re_6 = eg.Sum(y => y.total_reward_6),
                                 re_7 = eg.Sum(y => y.total_reward_7),
                                 re_8 = eg.Sum(y => y.total_reward_8),
                                 re_9 = eg.Sum(y => y.total_reward_9),
                                 re_10 = eg.Sum(y => y.total_reward_10),
                                 re_11 = eg.Sum(y => y.total_reward_11),
                                 re_12 = eg.Sum(y => y.total_reward_12),
                                 re_13 = eg.Sum(y => y.total_reward_13),
                                 re_14 = eg.Sum(y => y.total_reward_14),
                                 re_15 = eg.Sum(y => y.total_reward_15),
                                 re_16 = eg.Sum(y => y.total_reward_16)

                             }).FirstOrDefault();
                //total_all
                total_all = new List<int>();
                if (query != null)
                {
                    total_all.Add(query.re_1);
                    total_all.Add(query.re_2);
                    total_all.Add(query.re_3);
                    total_all.Add(query.re_4);
                    total_all.Add(query.re_5);
                    total_all.Add(query.re_6);
                    total_all.Add(query.re_7);
                    total_all.Add(query.re_8);
                    total_all.Add(query.re_9);
                    total_all.Add(query.re_10);
                    total_all.Add(query.re_11);
                    total_all.Add(query.re_12);
                    total_all.Add(query.re_13);
                    total_all.Add(query.re_14);
                    total_all.Add(query.re_15);
                    total_all.Add(query.re_16);
                }
                return result.ToPagedList(pageindex, pagesize);
            }
        }

        public List<proc_employee_reward_collective_total_Result> getListEmployee_collective_export_total(string text_search, DateTime? from_date, DateTime? to_date, string list_id, ref List<int> total_all, ref List<int> total_skill_reward_1, ref List<int> total_skill_reward_2, ref List<int> total_skill_reward_3)
        {
            using (var data = new baseEntities())
            {

                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                var total_collective = data.employee_reward_collective_total(text_search, from_date, to_date);

                var list = (from p in total_collective
                            select p).ToList();
                List<int> arr_id=new List<int>();
                //get by list id department
                if (!string.IsNullOrEmpty(list_id))
                {
                    var arr = list_id.Split(',');
                    var arrs = arr.ToList();
                    if (arrs.Count > 0)
                    {

                        if (arrs[0] != "System.Int32[]")
                        {
                            for (int i = 0; i < arr.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(arrs[i]))
                                {
                                    if (isNumber(arr[i]))
                                    { 
                                        arr_id.Add(int.Parse(arrs[i]));   
                                    }
                                }
                            }
                        }

                    }
                }

                if (arr_id.Count > 0)
                {
                    list = list.Where(p => arr_id.Contains(p.departments_id ?? 0)).ToList();
                }

                list = list.OrderBy(p => p.departments_id).ToList();

                IQueryable<proc_employee_reward_collective_total_Result> result = (from p in list
                                                                                   select new proc_employee_reward_collective_total_Result
                                                                                   {
                                                                                       departments_id = p.departments_id,
                                                                                       total_reward = p.total_reward,
                                                                                       total_reward_1 = p.total_reward_1,
                                                                                       total_reward_2 = p.total_reward_2,
                                                                                       total_reward_3 = p.total_reward_3,
                                                                                       total_reward_4 = p.total_reward_4,
                                                                                       total_reward_5 = p.total_reward_5,
                                                                                       total_reward_6 = p.total_reward_6,
                                                                                       total_reward_7 = p.total_reward_7,
                                                                                       total_reward_8 = p.total_reward_8,
                                                                                       total_reward_9 = p.total_reward_9,
                                                                                       total_reward_10 = p.total_reward_10,
                                                                                       total_reward_11 = p.total_reward_11,
                                                                                       total_reward_12 = p.total_reward_12,
                                                                                       total_reward_13 = p.total_reward_13,
                                                                                       total_reward_14 = p.total_reward_14,
                                                                                       total_reward_15 = p.total_reward_15,
                                                                                       total_reward_16 = p.total_reward_16,
                                                                                       departments_name = p.departments_name
                                                                                   }).AsQueryable();

                //total_all
                total_all = new List<int>();


                var query = (from e in result
                             group e by 1 into eg
                             select new
                             {
                                 TypeOfInvestment = eg.Key,
                                 re_1 = eg.Sum(y => y.total_reward_1),
                                 re_2 = eg.Sum(y => y.total_reward_2),
                                 re_3 = eg.Sum(y => y.total_reward_3),
                                 re_4 = eg.Sum(y => y.total_reward_4),
                                 re_5 = eg.Sum(y => y.total_reward_5),
                                 re_6 = eg.Sum(y => y.total_reward_6),
                                 re_7 = eg.Sum(y => y.total_reward_7),
                                 re_8 = eg.Sum(y => y.total_reward_8),
                                 re_9 = eg.Sum(y => y.total_reward_9),
                                 re_10 = eg.Sum(y => y.total_reward_10),
                                 re_11 = eg.Sum(y => y.total_reward_11),
                                 re_12 = eg.Sum(y => y.total_reward_12),
                                 re_13 = eg.Sum(y => y.total_reward_13),
                                 re_14=eg.Sum(y=>y.total_reward_14),
                                 re_15=eg.Sum(y=>y.total_reward_15),
                                 re_16=eg.Sum(y=>y.total_reward_16)

                             }).FirstOrDefault();
                //total_all
                if (query != null)
                {
                    total_all = new List<int>();
                    total_all.Add(query.re_1);
                    total_all.Add(query.re_2);
                    total_all.Add(query.re_3);
                    total_all.Add(query.re_4);
                    total_all.Add(query.re_5);
                    total_all.Add(query.re_6);
                    total_all.Add(query.re_7);
                    total_all.Add(query.re_8);
                    total_all.Add(query.re_9);
                    total_all.Add(query.re_10);
                    total_all.Add(query.re_11);
                    total_all.Add(query.re_12);
                    total_all.Add(query.re_13);
                    total_all.Add(query.re_14);
                    total_all.Add(query.re_15);
                    total_all.Add(query.re_16);
                }
                return result.ToList();
            }
        }

        public List<employee> GetListEmployeeAutoCompleted(string text, int start, int limit)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.employees
                            where p.full_name.Contains(text)
                            select p);//.Skip(start).Take(limit);

                list = list.OrderBy(p => p.employee_id);

                return list.Skip(start).Take(limit).ToList();
            }
        }

        public void deleteEmployee(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var em = data.employees.Where(p => p.employee_id == employee_id).FirstOrDefault();

                if (em != null)
                {
                    deleteEmployeeFamilyByEmID(em.employee_id);
                    deleteEmployeeForeignCountriesByEmID(em.employee_id);
                    deleteEmployeeInsuranceByEmID(em.employee_id);
                    deleteEmployeeJobSeveranceByEmID(em.employee_id);
                    deleteEmployeeMedicalByEmID(em.employee_id);
                    deleteEmployeeRotationByEmID(em.employee_id);
                    deleteEmployeeSchoolByEmID(em.employee_id);
                    deleteEmployeeVisaPassportByEmID(em.employee_id);
                    deleteEmployeeLeaveByEmID(em.employee_id);
                    deleteEmployeeContractByEmID(em.employee_id);
                    h_content_service conSvc=new h_content_service();
                    conSvc.deleteDepartmentEmployee(em.employee_id);

                    //Xoa employee object
                    data.DeleteObject(em);
                    data.SaveChanges();
                }

                
            }
        }

        public void deleteEmployeeFamilyByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_family = data.employee_family.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_family)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeFamilyByID(int family_id)
        {
            using (var data = new baseEntities())
            {
                var em_family = data.employee_family.Where(p => p.family_id == family_id).FirstOrDefault();

                data.DeleteObject(em_family);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeForeignCountriesByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_foreign = data.employee_foreign_countries.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_foreign)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeForeignCountriesByID(int foreign_countries)
        {
            using (var data = new baseEntities())
            {
                var em_foreign = data.employee_foreign_countries.Where(p => p.foreign_countries == foreign_countries).FirstOrDefault();

                data.DeleteObject(em_foreign);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeInsuranceByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_insurance = data.employee_insurance.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_insurance)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeInsuranceByID(int insurance_id)
        {
            using (var data = new baseEntities())
            {
                var em_insurance = data.employee_insurance.Where(p => p.insurance_id == insurance_id).FirstOrDefault();

                data.DeleteObject(em_insurance);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeJobSeveranceByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_job_severance = data.employee_job_severance.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_job_severance)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeJobSeveranceByID(int job_severance_id)
        {
            using (var data = new baseEntities())
            {
                var em_job_severance = data.employee_job_severance.Where(p => p.job_severance_id == job_severance_id).FirstOrDefault();

                data.DeleteObject(em_job_severance);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeMedicalByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_Medical = data.employee_medical_records.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_Medical)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeMedicalByID(int medical_records_id)
        {
            using (var data = new baseEntities())
            {
                var em_Medical = data.employee_medical_records.Where(p => p.medical_records_id == medical_records_id).FirstOrDefault();

                data.DeleteObject(em_Medical);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeRotationByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_rotation = data.employee_rotation.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_rotation)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeRotationByID(int rotation_id)
        {
            using (var data = new baseEntities())
            {
                var em_rotation = data.employee_rotation.Where(p => p.rotation_id == rotation_id).FirstOrDefault();

                data.DeleteObject(em_rotation);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeSchoolByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_school = data.employee_school.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_school)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeSchoolByID(int school_id)
        {
            using (var data = new baseEntities())
            {
                var em_school = data.employee_school.Where(p => p.employee_school_id == school_id).FirstOrDefault();

                data.DeleteObject(em_school);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeVisaPassportByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_passport = data.employee_visa_passport.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_passport)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeVisaPassportByID(int passport_id)
        {
            using (var data = new baseEntities())
            {
                var em_passport = data.employee_visa_passport.Where(p => p.passport_id == passport_id).FirstOrDefault();

                data.DeleteObject(em_passport);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeLeaveByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_leave = data.leave_manage.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_leave)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteEmployeeLeaveByID(int leave_id)
        {
            using (var data = new baseEntities())
            {
                var em_leave = data.leave_manage.Where(p => p.leave_id == leave_id).FirstOrDefault();

                data.DeleteObject(em_leave);
                data.SaveChanges();
            }
        }

        public void deleteEmployeeContractByEmID(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_contract = data.contracts.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list_contract)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public void deleteContractByID(int contract_id)
        {
            using (var data = new baseEntities())
            {
                var con = data.contracts.Where(p => p.contract_id == contract_id).FirstOrDefault();

                data.DeleteObject(con);
                data.SaveChanges();
            }
        }

        public int deleteObjectEmployeeByID(int object_employee_id)
        {
            using (var data = new baseEntities())
            {
                var check = data.employees.Where(p => p.object_employee_id == object_employee_id).ToList();
                if (check.Count > 0)
                {
                    //Can't delete object
                    return 0;
                }
                else
                {
                    var con = data.object_employee.Where(p => p.object_employee_id == object_employee_id).FirstOrDefault();

                    data.DeleteObject(con);
                    data.SaveChanges();
                    return 1;
                }
                
            }
        }

        public int deleteTypeContractByID(int type_contract_id)
        {
            using (var data = new baseEntities())
            {
                //delete all contract of type_contract_id
                var all_con = data.contracts.Where(p => p.type_contract_id == type_contract_id).ToList();
                if (all_con.Count > 0)
                {
                    //Can't delete type contract
                    return 0;
                }
                else
                {
                    var con = data.type_contract.Where(p => p.type_contract_id == type_contract_id).FirstOrDefault();

                    data.DeleteObject(con);
                    data.SaveChanges();
                    return 1;
                }

                
            }
        }

        public int deleteTypeLeaveByID(int type_leave_id)
        {
            using (var data = new baseEntities())
            {
                //delete all contract of type_contract_id
                var all_con = data.leave_manage.Where(p => p.type_leave_id == type_leave_id).ToList();
                if (all_con.Count > 0)
                {
                    //Can't delete type leave
                    return 0;
                }
                else
                {
                    var con = data.type_leave.Where(p => p.type_leave_id == type_leave_id).FirstOrDefault();

                    data.DeleteObject(con);
                    data.SaveChanges();
                    return 1;
                }
            }
        }

        public int deleteTypeRewardByID(int type_reward_id)
        {
            using (var data = new baseEntities())
            {
                 //delete all contract of type_contract_id
                var all_con = data.rewards.Where(p => p.type_medals == type_reward_id.ToString() || p.medal_class==type_reward_id.ToString()).ToList();
                if (all_con.Count > 0)
                {
                    //Can't delete type reward
                    return 0;
                }
                else
                {
                    var con = data.type_reward.Where(p => p.type_reward_id == type_reward_id).FirstOrDefault();

                    data.DeleteObject(con);
                    data.SaveChanges();
                    return 1;
                }
            }
        }

        public void deleteRewardByID(int reward_id)
        {
            using (var data = new baseEntities())
            {
                var con = data.rewards.Where(p => p.reward_id == reward_id).FirstOrDefault();

                data.DeleteObject(con);
                data.SaveChanges();
            }
        }
    }
}
